package com.heaven.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;

import com.heaven.model.Account;

public class AccountDao implements Dao<Account> {
	private JdbcTemplate jdbcTemplate;	
	
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	
	public Account select(int accountNo) throws ClassNotFoundException, SQLException {
		Connection c = getConnection();
		String sql = "select * from Accounts where ACCOUNT_NO = ?";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setInt(1, accountNo);
		
		ResultSet rs = ps.executeQuery();
		
		Account account = null;
		
		if(rs.next()) {
			account = new Account();
			account.setAccountNo(rs.getInt("ACCOUNT_NO"));
			account.setOwner(rs.getString("OWNER"));
			account.setBalance(rs.getInt("BALANCE"));
		}
		
		rs.close();
		ps.close();
		c.close();
		
		return account;
	}
	
	public int insert(Account account) throws ClassNotFoundException, SQLException {
		Connection c = getConnection();
		String sql = "insert into Accounts (OWNER, BALANCE) values (?, ?)";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setString(1, account.getOwner());
		ps.setInt(2,  account.getBalance());
		
		int count = ps.executeUpdate();
			
		ps.close();
		c.close();
		
		return count;
	}
	
	public int update(Account account) throws ClassNotFoundException, SQLException {
		Connection c = getConnection();
		String sql = "update Accounts set OWNER = ?, BALANCE = ? where ACCOUNT_NO = ?";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setString(1, account.getOwner());
		ps.setInt(2,  account.getBalance());
		ps.setInt(3, account.getAccountNo());
		
		int count = ps.executeUpdate();
		
		ps.close();
		c.close();
		
		return count;
	}
	
	public int delete(Account account) throws ClassNotFoundException, SQLException {
		Connection c = getConnection();
		String sql = "delete from Accounts where ACCOUNT_NO = ?";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setInt(1, account.getAccountNo());
		
		int count = ps.executeUpdate();
		
		ps.close();
		c.close();
		
		return count;
	}
	
	public List<Account> selectAll() throws ClassNotFoundException, SQLException {
		Connection c = getConnection();
		String sql = "select * from Accounts";
		PreparedStatement ps = c.prepareStatement(sql);
		
		ResultSet rs = ps.executeQuery();
		
		List<Account> accounts = new ArrayList<Account>();
		
		Account account = null;
		
		while(rs.next()) {
			account = new Account();
			account.setAccountNo(rs.getInt("ACCOUNT_NO"));
			account.setOwner(rs.getString("OWNER"));
			account.setBalance(rs.getInt("BALANCE"));
			
			accounts.add(account);
		}		
		
		return accounts;
	}

	public int deleteAll() throws ClassNotFoundException, SQLException {
		/*
		Connection c = getConnection();
		String sql = "delete from ACCOUNTS";
		PreparedStatement ps = c.prepareStatement(sql);
		
		int count = ps.executeUpdate();
		
		ps.close();
		c.close();
		
		return count;
		*/
		return this.jdbcTemplate.update("delete from ACCOUNTS");
	}

	private Connection getConnection() throws ClassNotFoundException {
		Connection c = null;
		
		try {
			Class.forName("org.hsqldb.jdbcDriver");
			c = DriverManager.getConnection("jdbc:hsqldb:D:/MyDB;shutdown=true", "sa", "");
		} catch (SQLException ex) {
			System.out.println(ex.getMessage());
		}

		return c;
	}
	

	public Account selectByOwner(String owner) throws ClassNotFoundException, SQLException {
		Connection c = getConnection();
		String sql = "select * from Accounts where OWNER = ?";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setString(1,  owner);
		
		ResultSet rs = ps.executeQuery();
		
		Account account = null;
		
		if(rs.next()) {
			account = new Account();
			account.setAccountNo(rs.getInt("ACCOUNT_NO"));
			account.setOwner(rs.getString("OWNER"));
			account.setBalance(rs.getInt("BALANCE"));
		}
		
		rs.close();
		ps.close();
		c.close();
		
		return account;
	}
}